"""
简化的数据库管理器
Simple Database Manager

提供基本的数据库操作功能：
- SQLite数据库连接管理
- 基本的CRUD操作
- 简化的事务处理
- 摄像头数据表管理
"""
import sqlite3
import logging
from typing import List, Optional, Dict, Any, Tuple
from datetime import datetime
from contextlib import contextmanager
from pathlib import Path

from config.settings import settings

logger = logging.getLogger(__name__)


class DatabaseManager:
    """简化的数据库管理器"""
    
    def __init__(self, db_path: Optional[str] = None):
        # 从DATABASE_URL中提取SQLite路径
        if db_path:
            self.db_path = db_path
        else:
            db_url = settings.DATABASE_URL
            if db_url.startswith("sqlite:///"):
                self.db_path = db_url.replace("sqlite:///", "")
            else:
                # 默认路径
                self.db_path = str(Path(settings.DATA_DIR) / "campus_twin.db")
        
        self._ensure_database_exists()
        self._create_tables()
    
    def _ensure_database_exists(self):
        """确保数据库文件存在"""
        db_file = Path(self.db_path)
        db_file.parent.mkdir(parents=True, exist_ok=True)
        
        if not db_file.exists():
            logger.info(f"创建数据库文件: {self.db_path}")
    
    def _create_tables(self):
        """创建数据表"""
        try:
            with self.get_connection() as conn:
                # 创建摄像头表
                conn.execute('''
                    CREATE TABLE IF NOT EXISTS cameras (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        name TEXT NOT NULL UNIQUE,
                        longitude REAL NOT NULL DEFAULT 0.0,
                        latitude REAL NOT NULL DEFAULT 0.0,
                        height REAL DEFAULT 10.0,
                        location TEXT,
                        rtsp_url TEXT,
                        wvp_device_id TEXT,
                        wvp_channel_id TEXT,
                        description TEXT,
                        ai_enabled BOOLEAN DEFAULT FALSE,
                        status TEXT DEFAULT 'offline',
                        stream_status TEXT DEFAULT 'inactive',
                        ai_algorithms TEXT,
                        processing_fps INTEGER DEFAULT 3,
                        priority_level INTEGER DEFAULT 5,
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        last_frame_time TIMESTAMP,
                        reconnect_count INTEGER DEFAULT 0,
                        last_error TEXT
                    )
                ''')
                
                # 创建索引
                conn.execute('CREATE INDEX IF NOT EXISTS idx_cameras_name ON cameras(name)')
                conn.execute('CREATE INDEX IF NOT EXISTS idx_cameras_status ON cameras(status)')
                conn.execute('CREATE INDEX IF NOT EXISTS idx_cameras_wvp ON cameras(wvp_device_id)')
                conn.execute('CREATE INDEX IF NOT EXISTS idx_cameras_location ON cameras(longitude, latitude)')
                
                # 创建WVP设备表
                conn.execute('''
                    CREATE TABLE IF NOT EXISTS wvp_devices (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        device_id TEXT NOT NULL UNIQUE,
                        device_name TEXT,
                        manufacturer TEXT,
                        model TEXT,
                        firmware TEXT,
                        transport TEXT,
                        stream_mode TEXT,
                        ip TEXT,
                        port INTEGER,
                        host_address TEXT,
                        expires INTEGER,
                        register_time TIMESTAMP,
                        keepalive_time TIMESTAMP,
                        channel_count INTEGER DEFAULT 0,
                        status TEXT DEFAULT 'offline',
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                    )
                ''')
                
                # 创建WVP通道表
                conn.execute('''
                    CREATE TABLE IF NOT EXISTS wvp_channels (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        device_id TEXT NOT NULL,
                        channel_id TEXT NOT NULL,
                        channel_name TEXT,
                        manufacturer TEXT,
                        model TEXT,
                        owner TEXT,
                        civil_code TEXT,
                        block TEXT,
                        address TEXT,
                        parental INTEGER DEFAULT 0,
                        parent_id TEXT,
                        safety_way INTEGER DEFAULT 0,
                        register_way INTEGER DEFAULT 1,
                        cert_num TEXT,
                        certifiable INTEGER DEFAULT 0,
                        err_code INTEGER DEFAULT 0,
                        end_time TIMESTAMP,
                        secrecy TEXT,
                        ip_address TEXT,
                        port INTEGER,
                        password TEXT,
                        ptz_type INTEGER DEFAULT 0,
                        status TEXT DEFAULT 'offline',
                        longitude REAL DEFAULT 0.0,
                        latitude REAL DEFAULT 0.0,
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        UNIQUE(device_id, channel_id)
                    )
                ''')
                
                # 创建AI结果表（简化版）
                conn.execute('''
                    CREATE TABLE IF NOT EXISTS ai_results (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        camera_id INTEGER NOT NULL,
                        algorithm TEXT NOT NULL,
                        result_data TEXT,
                        confidence REAL,
                        processing_time REAL,
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        FOREIGN KEY (camera_id) REFERENCES cameras (id) ON DELETE CASCADE
                    )
                ''')
                
                # 创建用户表（简化版）
                conn.execute('''
                    CREATE TABLE IF NOT EXISTS users (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        username TEXT NOT NULL UNIQUE,
                        password_hash TEXT NOT NULL,
                        email TEXT,
                        full_name TEXT,
                        role TEXT DEFAULT 'user',
                        is_active BOOLEAN DEFAULT TRUE,
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        last_login TIMESTAMP
                    )
                ''')
                
                # 创建系统配置表
                conn.execute('''
                    CREATE TABLE IF NOT EXISTS system_config (
                        id INTEGER PRIMARY KEY AUTOINCREMENT,
                        config_key TEXT NOT NULL UNIQUE,
                        config_value TEXT,
                        config_type TEXT DEFAULT 'string',
                        description TEXT,
                        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                    )
                ''')
                
                # 创建索引
                conn.execute('CREATE INDEX IF NOT EXISTS idx_wvp_devices_device_id ON wvp_devices(device_id)')
                conn.execute('CREATE INDEX IF NOT EXISTS idx_wvp_devices_status ON wvp_devices(status)')
                conn.execute('CREATE INDEX IF NOT EXISTS idx_wvp_channels_device ON wvp_channels(device_id)')
                conn.execute('CREATE INDEX IF NOT EXISTS idx_wvp_channels_channel ON wvp_channels(channel_id)')
                conn.execute('CREATE INDEX IF NOT EXISTS idx_wvp_channels_status ON wvp_channels(status)')
                conn.execute('CREATE INDEX IF NOT EXISTS idx_ai_results_camera ON ai_results(camera_id)')
                conn.execute('CREATE INDEX IF NOT EXISTS idx_ai_results_algorithm ON ai_results(algorithm)')
                conn.execute('CREATE INDEX IF NOT EXISTS idx_users_username ON users(username)')
                conn.execute('CREATE INDEX IF NOT EXISTS idx_users_role ON users(role)')
                conn.execute('CREATE INDEX IF NOT EXISTS idx_system_config_key ON system_config(config_key)')
                
                conn.commit()
                logger.info("数据表创建完成")
                
        except Exception as e:
            logger.error(f"创建数据表失败: {e}")
            raise
    
    @contextmanager
    def get_connection(self):
        """获取数据库连接上下文管理器"""
        conn = None
        try:
            conn = sqlite3.connect(self.db_path)
            conn.row_factory = sqlite3.Row  # 使结果可以按列名访问
            yield conn
        except Exception as e:
            if conn:
                conn.rollback()
            logger.error(f"数据库操作异常: {e}")
            raise
        finally:
            if conn:
                conn.close()
    
    def execute_query(self, query: str, params: Tuple = ()) -> List[Dict[str, Any]]:
        """执行查询并返回结果"""
        try:
            with self.get_connection() as conn:
                cursor = conn.execute(query, params)
                rows = cursor.fetchall()
                return [dict(row) for row in rows]
        except Exception as e:
            logger.error(f"执行查询失败: {query} - {e}")
            raise
    
    def execute_update(self, query: str, params: Tuple = ()) -> int:
        """执行更新操作并返回影响的行数"""
        try:
            with self.get_connection() as conn:
                cursor = conn.execute(query, params)
                conn.commit()
                return cursor.rowcount
        except Exception as e:
            logger.error(f"执行更新失败: {query} - {e}")
            raise
    
    def execute_insert(self, query: str, params: Tuple = ()) -> int:
        """执行插入操作并返回新记录的ID"""
        try:
            with self.get_connection() as conn:
                cursor = conn.execute(query, params)
                conn.commit()
                return cursor.lastrowid
        except Exception as e:
            logger.error(f"执行插入失败: {query} - {e}")
            raise
    
    # 摄像头相关操作
    
    def create_camera(self, camera_data: Dict[str, Any]) -> int:
        """创建摄像头记录"""
        query = '''
            INSERT INTO cameras (
                name, longitude, latitude, height, location, rtsp_url,
                wvp_device_id, wvp_channel_id, description, ai_enabled,
                status, ai_algorithms, processing_fps, priority_level
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        '''
        
        params = (
            camera_data.get('name'),
            camera_data.get('longitude', 0.0),
            camera_data.get('latitude', 0.0),
            camera_data.get('height', 10.0),
            camera_data.get('location'),
            camera_data.get('rtsp_url'),
            camera_data.get('wvp_device_id'),
            camera_data.get('wvp_channel_id'),
            camera_data.get('description'),
            camera_data.get('ai_enabled', False),
            camera_data.get('status', 'offline'),
            camera_data.get('ai_algorithms'),
            camera_data.get('processing_fps', 3),
            camera_data.get('priority_level', 5)
        )
        
        return self.execute_insert(query, params)
    
    def get_camera_by_id(self, camera_id: int) -> Optional[Dict[str, Any]]:
        """根据ID获取摄像头"""
        query = 'SELECT * FROM cameras WHERE id = ?'
        results = self.execute_query(query, (camera_id,))
        return results[0] if results else None
    
    def get_camera_by_name(self, name: str) -> Optional[Dict[str, Any]]:
        """根据名称获取摄像头"""
        query = 'SELECT * FROM cameras WHERE name = ?'
        results = self.execute_query(query, (name,))
        return results[0] if results else None
    
    def get_camera_by_wvp_device(self, device_id: str, channel_id: Optional[str] = None) -> Optional[Dict[str, Any]]:
        """根据WVP设备ID获取摄像头"""
        if channel_id:
            query = 'SELECT * FROM cameras WHERE wvp_device_id = ? AND wvp_channel_id = ?'
            params = (device_id, channel_id)
        else:
            query = 'SELECT * FROM cameras WHERE wvp_device_id = ?'
            params = (device_id,)
        
        results = self.execute_query(query, params)
        return results[0] if results else None
    
    def update_camera(self, camera_id: int, update_data: Dict[str, Any]) -> bool:
        """更新摄像头信息"""
        if not update_data:
            return False
        
        # 构建更新查询
        set_clauses = []
        params = []
        
        for field, value in update_data.items():
            if field == 'ai_algorithms' and isinstance(value, dict):
                set_clauses.append(f'{field} = ?')
                params.append(str(value))
            else:
                set_clauses.append(f'{field} = ?')
                params.append(value)
        
        set_clauses.append('updated_at = CURRENT_TIMESTAMP')
        params.append(camera_id)
        
        query = f'UPDATE cameras SET {", ".join(set_clauses)} WHERE id = ?'
        
        return self.execute_update(query, tuple(params)) > 0
    
    def delete_camera(self, camera_id: int) -> bool:
        """删除摄像头"""
        query = 'DELETE FROM cameras WHERE id = ?'
        return self.execute_update(query, (camera_id,)) > 0
    
    def get_camera_list(self, filters: Dict[str, Any] = None, 
                       page: int = 1, size: int = 10) -> Tuple[List[Dict[str, Any]], int]:
        """获取摄像头列表"""
        # 构建WHERE子句
        where_clauses = []
        params = []
        
        if filters:
            if filters.get('name'):
                where_clauses.append('name LIKE ?')
                params.append(f"%{filters['name']}%")
            
            if filters.get('location'):
                where_clauses.append('location LIKE ?')
                params.append(f"%{filters['location']}%")
            
            if filters.get('status'):
                where_clauses.append('status = ?')
                params.append(filters['status'])
            
            if filters.get('ai_enabled') is not None:
                where_clauses.append('ai_enabled = ?')
                params.append(filters['ai_enabled'])
            
            if filters.get('wvp_device_id'):
                where_clauses.append('wvp_device_id = ?')
                params.append(filters['wvp_device_id'])
        
        where_clause = ' WHERE ' + ' AND '.join(where_clauses) if where_clauses else ''
        
        # 获取总数
        count_query = f'SELECT COUNT(*) as total FROM cameras{where_clause}'
        count_result = self.execute_query(count_query, tuple(params))
        total = count_result[0]['total'] if count_result else 0
        
        # 获取分页数据
        offset = (page - 1) * size
        list_query = f'''
            SELECT * FROM cameras{where_clause} 
            ORDER BY created_at DESC 
            LIMIT ? OFFSET ?
        '''
        
        list_params = tuple(params) + (size, offset)
        cameras = self.execute_query(list_query, list_params)
        
        return cameras, total
    
    def get_cameras_by_status(self, status: str) -> List[Dict[str, Any]]:
        """根据状态获取摄像头列表"""
        query = 'SELECT * FROM cameras WHERE status = ? ORDER BY name'
        return self.execute_query(query, (status,))
    
    def get_ai_enabled_cameras(self, status_filter: Optional[str] = None) -> List[Dict[str, Any]]:
        """获取启用AI的摄像头列表"""
        if status_filter:
            query = 'SELECT * FROM cameras WHERE ai_enabled = TRUE AND status = ? ORDER BY name'
            params = (status_filter,)
        else:
            query = 'SELECT * FROM cameras WHERE ai_enabled = TRUE ORDER BY name'
            params = ()
        
        return self.execute_query(query, params)
    
    def batch_update_status(self, camera_ids: List[int], status: str) -> int:
        """批量更新摄像头状态"""
        if not camera_ids:
            return 0
        
        placeholders = ','.join(['?'] * len(camera_ids))
        query = f'''
            UPDATE cameras 
            SET status = ?, updated_at = CURRENT_TIMESTAMP 
            WHERE id IN ({placeholders})
        '''
        
        params = [status] + camera_ids
        return self.execute_update(query, tuple(params))
    
    def get_statistics(self) -> Dict[str, Any]:
        """获取摄像头统计信息"""
        stats_query = '''
            SELECT 
                COUNT(*) as total,
                SUM(CASE WHEN status = 'online' THEN 1 ELSE 0 END) as online,
                SUM(CASE WHEN status = 'offline' THEN 1 ELSE 0 END) as offline,
                SUM(CASE WHEN status = 'error' THEN 1 ELSE 0 END) as error,
                SUM(CASE WHEN status = 'maintenance' THEN 1 ELSE 0 END) as maintenance,
                SUM(CASE WHEN ai_enabled = TRUE THEN 1 ELSE 0 END) as ai_enabled
            FROM cameras
        '''
        
        result = self.execute_query(stats_query)
        return result[0] if result else {}
    
    # AI结果相关操作
    
    def save_ai_result(self, camera_id: int, algorithm: str, result_data: Dict[str, Any], 
                      confidence: float = None, processing_time: float = None) -> int:
        """保存AI分析结果"""
        query = '''
            INSERT INTO ai_results (camera_id, algorithm, result_data, confidence, processing_time)
            VALUES (?, ?, ?, ?, ?)
        '''
        
        params = (
            camera_id,
            algorithm,
            str(result_data),
            confidence,
            processing_time
        )
        
        return self.execute_insert(query, params)
    
    def get_ai_results(self, camera_id: int, algorithm: Optional[str] = None, 
                      limit: int = 100) -> List[Dict[str, Any]]:
        """获取AI分析结果"""
        if algorithm:
            query = '''
                SELECT * FROM ai_results 
                WHERE camera_id = ? AND algorithm = ? 
                ORDER BY created_at DESC 
                LIMIT ?
            '''
            params = (camera_id, algorithm, limit)
        else:
            query = '''
                SELECT * FROM ai_results 
                WHERE camera_id = ? 
                ORDER BY created_at DESC 
                LIMIT ?
            '''
            params = (camera_id, limit)
        
        return self.execute_query(query, params)
    
    def cleanup_old_ai_results(self, days: int = 30) -> int:
        """清理旧的AI结果"""
        query = '''
            DELETE FROM ai_results 
            WHERE created_at < datetime('now', '-{} days')
        '''.format(days)
        
        return self.execute_update(query)
    
    def test_connection(self) -> bool:
        """测试数据库连接"""
        try:
            with self.get_connection() as conn:
                conn.execute('SELECT 1')
                return True
        except Exception as e:
            logger.error(f"数据库连接测试失败: {e}")
            return False
    
    def get_database_info(self) -> Dict[str, Any]:
        """获取数据库信息"""
        try:
            with self.get_connection() as conn:
                # 获取表信息
                tables = conn.execute(
                    "SELECT name FROM sqlite_master WHERE type='table'"
                ).fetchall()
                
                table_info = {}
                for table in tables:
                    table_name = table['name']
                    count_result = conn.execute(f'SELECT COUNT(*) as count FROM {table_name}').fetchone()
                    table_info[table_name] = count_result['count']
                
                return {
                    "database_path": self.db_path,
                    "tables": table_info,
                    "connection_test": True
                }
        except Exception as e:
            logger.error(f"获取数据库信息失败: {e}")
            return {
                "database_path": self.db_path,
                "connection_test": False,
                "error": str(e)
            }


# 全局数据库管理器实例
_db_manager: Optional[DatabaseManager] = None


def get_database_manager() -> DatabaseManager:
    """获取数据库管理器实例"""
    global _db_manager
    if _db_manager is None:
        _db_manager = DatabaseManager()
    return _db_manager


def initialize_database():
    """初始化数据库"""
    try:
        db_manager = get_database_manager()
        logger.info("数据库初始化成功")
        return db_manager
    except Exception as e:
        logger.error(f"数据库初始化失败: {e}")
        raise